﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using OMG.Fill.Potato.Model.Ent;
using OMG.Fill.Potato.Services;
using ProjectShare;
using System;
using System.Collections.Generic;
using System.IO;
using System.Text;
using System.Web;
using System.Web.Http;
namespace OMG.Fill.Potato.Model.UI.Controllers
{
    public class PotPromotionController : ApiController
    {
        PotPromotionServices potPromotionServices = new PotPromotionServices();
        
        //[ApiActionAuthorize("获取马铃薯技术推广机构及人员情况统计表分页列表对象操作")]
        //[ApiActionException]
        public Pager<List<PotPromotionEntity>> PostPageList(PotPromotionQuery query)
        {
            return potPromotionServices.GetPageList(query);
        }
        //[ApiActionAuthorize("新增马铃薯技术推广机构及人员情况统计表指定对象操作")]
        //[ApiActionException]
        public ResResult<int> Post(PotPromotionEntity model) {
            return potPromotionServices.AddInfo(model);
        }
        //[ApiActionAuthorize("修改马铃薯技术推广机构及人员情况统计表指定对象操作")]
        //[ApiActionException]
        public ResResult<int> Put(PotPromotionEntity model)
        {
            return potPromotionServices.UpdateInfo(model);
        }
        //[ApiActionAuthorize("删除马铃薯技术推广机构及人员情况统计表指定对象操作")]
        //[ApiActionException]
        public ResResult<int> Delete(string id)
        {
            return potPromotionServices.DeleteInfo(id);
        }
        //[ApiActionAuthorize("获取马铃薯技术推广机构及人员情况统计表指定对象操作")]
        //[ApiActionException]
        public ResResult<PotPromotionEntity> Get(string ReportId)
        {
            return potPromotionServices.GetModel(ReportId);
        }

        public ResResult<PromotionFillModel> Get(string AreaCode, string FillInYear)
        {
            return potPromotionServices.GetModel(AreaCode, FillInYear);
        }

        /// <summary>
        /// 获取导出数据
        /// </summary>
        /// <param name="ReportId"></param>
        /// <returns></returns>
        public MemoryStream Method(string AreaCode, string FillInYear)
        {
            FileStream file = null;
            //创建下载Excel文件
            var workbook = new HSSFWorkbook();
            string fileIndex = HttpRuntime.AppDomainAppPath.ToString();
            string filename = @"tempfile\Basic" + DateTime.Now.Ticks.ToString() + ".xls";//临时文件，定期在tempfile手动删除
            string tempExcel = fileIndex + filename;//临时文件路径及全称
            string templateName = fileIndex + @"Excel\PotDownload.xls";//模板文件
            file = new FileStream(templateName, FileMode.Open, FileAccess.Read);//读模板文件
            HSSFWorkbook templateBook = new HSSFWorkbook(file);//读取模板文件信息转换成Excel工作薄
            HSSFSheet sheet = templateBook.GetSheet("PotPromotion") as HSSFSheet;//读取模板所在sheet位置
            PotPromotionEntity pagelist = potPromotionServices.GetModel(AreaCode, FillInYear).model.DataModel;//数据获取

            HSSFRow row = (HSSFRow)sheet.GetRow(4);//读取模板第5行
            var style = row.GetCell(2).CellStyle;//读取模板第5行第一单元格样式并保存
            row.GetCell(2).CellStyle = style;//对第二行第一单元格设置原来样式

            HSSFRow datarow1 = (HSSFRow)sheet.GetRow(4);
            datarow1.GetCell(2).SetCellValue(pagelist.InstituNum == null ? "" : pagelist.InstituNum.ToString());
            datarow1.GetCell(2).CellStyle = style;

            HSSFRow datarow2 = (HSSFRow)sheet.GetRow(5);
            datarow2.GetCell(2).SetCellValue(pagelist.InstituNum_City == null ? "" : pagelist.InstituNum_City.ToString());
            datarow2.GetCell(2).CellStyle = style;

            HSSFRow datarow3 = (HSSFRow)sheet.GetRow(6);
            datarow3.GetCell(2).SetCellValue(pagelist.InstituNum_Area == null ? "" : pagelist.InstituNum_Area.ToString());
            datarow3.GetCell(2).CellStyle = style;

            HSSFRow datarow4 = (HSSFRow)sheet.GetRow(7);
            datarow4.GetCell(2).SetCellValue(pagelist.InstituNum_town == null ? "" : pagelist.InstituNum_town.ToString());
            datarow4.GetCell(2).CellStyle = style;

            HSSFRow datarow5 = (HSSFRow)sheet.GetRow(8);
            datarow5.GetCell(2).SetCellValue(pagelist.PeopleNum == null ? "" : pagelist.PeopleNum.ToString());
            datarow5.GetCell(2).CellStyle = style;

            HSSFRow datarow6 = (HSSFRow)sheet.GetRow(9);
            datarow6.GetCell(2).SetCellValue(pagelist.PeopleNum_County == null ? "" : pagelist.PeopleNum_County.ToString());
            datarow6.GetCell(2).CellStyle = style;

            HSSFRow datarow7 = (HSSFRow)sheet.GetRow(10);
            datarow7.GetCell(2).SetCellValue(pagelist.PeopleNum_City == null ? "" : pagelist.PeopleNum_City.ToString());
            datarow7.GetCell(2).CellStyle = style;

            HSSFRow datarow8 = (HSSFRow)sheet.GetRow(11);
            datarow8.GetCell(2).SetCellValue(pagelist.PeopleNum_man == null ? "" : pagelist.PeopleNum_man.ToString());
            datarow8.GetCell(2).CellStyle = style;

            HSSFRow datarow9 = (HSSFRow)sheet.GetRow(12);
            datarow9.GetCell(2).SetCellValue(pagelist.PeopleNum_woman == null ? "" : pagelist.PeopleNum_woman.ToString());
            datarow9.GetCell(2).CellStyle = style;

            HSSFRow datarow10 = (HSSFRow)sheet.GetRow(14);
            datarow10.GetCell(2).SetCellValue(pagelist.PeopleNum_age1 == null ? "" : pagelist.PeopleNum_age1.ToString());
            datarow10.GetCell(2).CellStyle = style;

            HSSFRow datarow11 = (HSSFRow)sheet.GetRow(15);
            datarow11.GetCell(2).SetCellValue(pagelist.PeopleNum_age2 == null ? "" : pagelist.PeopleNum_age2.ToString());
            datarow11.GetCell(2).CellStyle = style;

            HSSFRow datarow12 = (HSSFRow)sheet.GetRow(16);
            datarow12.GetCell(2).SetCellValue(pagelist.PeopleNum_age3 == null ? "" : pagelist.PeopleNum_age3.ToString());
            datarow12.GetCell(2).CellStyle = style;

            HSSFRow datarow13 = (HSSFRow)sheet.GetRow(17);
            datarow13.GetCell(2).SetCellValue(pagelist.PeopleNum_age4 == null ? "" : pagelist.PeopleNum_age4.ToString());
            datarow13.GetCell(2).CellStyle = style;

            HSSFRow datarow14 = (HSSFRow)sheet.GetRow(19);
            datarow14.GetCell(2).SetCellValue(pagelist.PeopleNum_xlss == null ? "" : pagelist.PeopleNum_xlss.ToString());
            datarow14.GetCell(2).CellStyle = style;

            HSSFRow datarow15 = (HSSFRow)sheet.GetRow(20);
            datarow15.GetCell(2).SetCellValue(pagelist.PeopleNum_xlbk == null ? "" : pagelist.PeopleNum_xlbk.ToString());
            datarow15.GetCell(2).CellStyle = style;

            HSSFRow datarow16 = (HSSFRow)sheet.GetRow(21);
            datarow16.GetCell(2).SetCellValue(pagelist.PeopleNum_xldz == null ? "" : pagelist.PeopleNum_xldz.ToString());
            datarow16.GetCell(2).CellStyle = style;

            HSSFRow datarow17 = (HSSFRow)sheet.GetRow(22);
            datarow17.GetCell(2).SetCellValue(pagelist.PeopleNum_xlzz == null ? "" : pagelist.PeopleNum_xlzz.ToString());
            datarow17.GetCell(2).CellStyle = style;

            HSSFRow datarow18 = (HSSFRow)sheet.GetRow(23);
            datarow18.GetCell(2).SetCellValue(pagelist.PeopleNum_xlqt == null ? "" : pagelist.PeopleNum_xlqt.ToString());
            datarow18.GetCell(2).CellStyle = style;

            HSSFRow datarow19 = (HSSFRow)sheet.GetRow(25);
            datarow19.GetCell(2).SetCellValue(pagelist.PeopleNum_zczgj == null ? "" : pagelist.PeopleNum_zczgj.ToString());
            datarow19.GetCell(2).CellStyle = style;

            HSSFRow datarow20 = (HSSFRow)sheet.GetRow(26);
            datarow20.GetCell(2).SetCellValue(pagelist.PeopleNum_zcfgj == null ? "" : pagelist.PeopleNum_zcfgj.ToString());
            datarow20.GetCell(2).CellStyle = style;

            HSSFRow datarow21 = (HSSFRow)sheet.GetRow(27);
            datarow21.GetCell(2).SetCellValue(pagelist.PeopleNum_zczj == null ? "" : pagelist.PeopleNum_zczj.ToString());
            datarow21.GetCell(2).CellStyle = style;

            HSSFRow datarow22 = (HSSFRow)sheet.GetRow(28);
            datarow22.GetCell(2).SetCellValue(pagelist.PeopleNum_zccj == null ? "" : pagelist.PeopleNum_zccj.ToString());
            datarow22.GetCell(2).CellStyle = style;

            HSSFRow datarow23 = (HSSFRow)sheet.GetRow(29);
            datarow23.GetCell(2).SetCellValue(pagelist.PeopleNum_zcqt == null ? "" : pagelist.PeopleNum_zcqt.ToString());
            datarow23.GetCell(2).CellStyle = style;

            sheet.CopyTo(workbook, "Sheet1", true, true);
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return ms;
            }
        }

        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="ReportId"></param>
        /// <param name="FillInYear"></param>
        /// <param name="AreaName"></param>
        public void GetExcelData(string FillInYear, string AreaCode)
        {
            AreaServices areaServices = new AreaServices();
            string AreaName = areaServices.GetByCodeModel(AreaCode).model.Name;
            //打开完成excel文件并下载
            HttpContext curContext = HttpContext.Current;
            // 设置编码和附件格式
            curContext.Response.AddHeader("Access-Control-Allow-Origin", "*");
            curContext.Response.ContentType = "application/vnd.ms-excel";
            curContext.Response.ContentEncoding = Encoding.UTF8;
            curContext.Response.Charset = "";            
            string strFileName = "（七）" + FillInYear + "-" + AreaName + "-马铃薯技术推广机构及人员情况统计表.xls";
            curContext.Response.AppendHeader("Content-Disposition",
                "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));           
            curContext.Response.BinaryWrite(Method(AreaCode, FillInYear).GetBuffer());
            curContext.Response.End();
        }

        /// <summary>
        /// 导入
        /// </summary>
        /// <param name="ReportId"></param>
        /// <param name="AreaCode"></param>
        /// <returns></returns>
        public ResResult<int> PostImportData(string ReportId,string AreaCode)
        {
            ReportServices reportServices = new ReportServices();
            ResResult<int> obj = new ResResult<int>();
            PotPromotionEntity pageModel = new PotPromotionEntity();
            DictServices dictServices = new DictServices();
            ResResult<int> SingleObj = new ResResult<int>();
            HttpContext curContext = HttpContext.Current;
            HttpFileCollection files = curContext.Request.Files;
            if (curContext.Request.Files.Count != 0)
            {
                HttpPostedFile upfile = files[0];
                if (upfile != null && upfile.ContentLength > 0)
                {
                    string temppath = curContext.Server.MapPath("~/App_Data/temppath");
                    if (!Directory.Exists(temppath))//判断文件夹是否存在 
                    {
                        Directory.CreateDirectory(temppath);//不存在则创建文件夹 
                    }
                    string filetype = Path.GetExtension(upfile.FileName);
                    string filenewName = Guid.NewGuid().ToString().Replace("-", "") + filetype;
                    string strpath = Path.Combine(temppath, filenewName);
                    upfile.SaveAs(strpath);
                   
                        bool isCity;  //是否是州市级                   
                        if (AreaCode.Substring(4, 8) == "00000000")
                        {
                            isCity = true;
                        }
                        else
                        {
                            isCity = false;
                        }

                        pageModel.ReportId = ReportId;
                        IWorkbook workbook = null;
                        ISheet sheet = null;
                        FileStream fs = new FileStream(strpath, FileMode.Open, FileAccess.Read);
                        switch (filetype)
                        {
                            case ".xls":
                                workbook = new HSSFWorkbook(fs);
                                break;
                            case ".xlsx":
                                workbook = new XSSFWorkbook(fs);
                                break;
                            default:
                                workbook = new HSSFWorkbook(fs);
                                break;
                        }
                        sheet = workbook.GetSheetAt(0);
                        HSSFRow datarow1 = (HSSFRow)sheet.GetRow(4);
                        if (datarow1.GetCell(2) != null)
                        {
                            if (datarow1.GetCell(2).CellType == CellType.Formula)
                            {
                                pageModel.InstituNum = (int)(datarow1.GetCell(2).NumericCellValue);
                            }
                            else
                            {
                                if (DataCheck.isNumber(datarow1.GetCell(2).ToString()))
                                {
                                    pageModel.InstituNum = int.Parse(datarow1.GetCell(2).ToString());
                                }

                            }
                        }
                        else
                        {
                            pageModel.InstituNum = 0;
                        }



                        if (isCity)
                        {
                            HSSFRow datarow2 = (HSSFRow)sheet.GetRow(5);
                            if (datarow2.GetCell(2) != null)
                            {
                                if (datarow2.GetCell(2).CellType == CellType.Formula)
                                {
                                    pageModel.InstituNum_City = (int)(datarow2.GetCell(2).NumericCellValue);
                                }
                                else
                                {
                                    if (DataCheck.isNumber(datarow2.GetCell(2).ToString()))
                                    {
                                        pageModel.InstituNum_City = int.Parse(datarow2.GetCell(2).ToString());
                                    }
                                }
                            }
                            else
                            {
                                pageModel.InstituNum_City = 0;
                            }
                        }


                        if (!isCity)
                        {
                            HSSFRow datarow3 = (HSSFRow)sheet.GetRow(6);
                            if (datarow3.GetCell(2) != null)
                            {
                                if (datarow3.GetCell(2).CellType == CellType.Formula)
                                {
                                    pageModel.InstituNum_Area = (int)(datarow3.GetCell(2).NumericCellValue);
                                }
                                else
                                {
                                    if (DataCheck.isNumber(datarow3.GetCell(2).ToString()))
                                    {
                                        pageModel.InstituNum_Area = int.Parse(datarow3.GetCell(2).ToString());
                                    }

                                }
                            }
                            else
                            {
                                pageModel.InstituNum_Area = 0;
                            }



                            HSSFRow datarow4 = (HSSFRow)sheet.GetRow(7);
                            if (datarow4.GetCell(2) != null)
                            {
                                if (datarow4.GetCell(2).CellType == CellType.Formula)
                                {
                                    pageModel.InstituNum_town = (int)(datarow4.GetCell(2).NumericCellValue);
                                }
                                else
                                {
                                    if (DataCheck.isNumber(datarow4.GetCell(2).ToString()))
                                    {
                                        pageModel.InstituNum_town = int.Parse(datarow4.GetCell(2).ToString());
                                    }

                                }
                            }
                            else
                            {
                                pageModel.InstituNum_town = 0;
                            }
                        }


                        HSSFRow datarow5 = (HSSFRow)sheet.GetRow(8);
                        if (datarow5.GetCell(2) != null)
                        {
                            if (datarow5.GetCell(2).CellType == CellType.Formula)
                            {
                                pageModel.PeopleNum = (int)(datarow5.GetCell(2).NumericCellValue);
                            }
                            else
                            {
                                if (DataCheck.isNumber(datarow5.GetCell(2).ToString()))
                                {
                                    pageModel.PeopleNum = int.Parse(datarow5.GetCell(2).ToString());
                                }

                            }
                        }
                        else
                        {
                            pageModel.PeopleNum = 0;
                        }



                        if (!isCity)
                        {
                            HSSFRow datarow6 = (HSSFRow)sheet.GetRow(9);
                            if (datarow6.GetCell(2) != null)
                            {
                                if (datarow6.GetCell(2).CellType == CellType.Formula)
                                {
                                    pageModel.PeopleNum_County = (int)(datarow6.GetCell(2).NumericCellValue);
                                }
                                else
                                {
                                    if (DataCheck.isNumber(datarow6.GetCell(2).ToString()))
                                    {
                                        pageModel.PeopleNum_County = int.Parse(datarow6.GetCell(2).ToString());
                                    }

                                }
                            }
                            else
                            {
                                pageModel.PeopleNum_County = 0;
                            }
                        }


                        if (isCity)
                        {
                            HSSFRow datarow7 = (HSSFRow)sheet.GetRow(10);
                            if (datarow7.GetCell(2) != null)
                            {
                                if (datarow7.GetCell(2).CellType == CellType.Formula)
                                {
                                    pageModel.PeopleNum_City = (int)(datarow7.GetCell(2).NumericCellValue);
                                }
                                else
                                {
                                    if (DataCheck.isNumber(datarow7.GetCell(2).ToString()))
                                    {
                                        pageModel.PeopleNum_City = int.Parse(datarow7.GetCell(2).ToString());
                                    }

                                }
                            }
                            else
                            {
                                pageModel.PeopleNum_City = 0;
                            }
                        }



                        HSSFRow datarow8 = (HSSFRow)sheet.GetRow(11);
                        if (datarow8.GetCell(2) != null)
                        {
                            if (datarow8.GetCell(2).CellType == CellType.Formula)
                            {
                                pageModel.PeopleNum_man = (int)(datarow8.GetCell(2).NumericCellValue);
                            }
                            else
                            {
                                if (DataCheck.isNumber(datarow8.GetCell(2).ToString()))
                                {
                                    pageModel.PeopleNum_man = int.Parse(datarow8.GetCell(2).ToString());
                                }

                            }
                        }
                        else
                        {
                            pageModel.PeopleNum_man = 0;
                        }




                        HSSFRow datarow9 = (HSSFRow)sheet.GetRow(12);
                        if (datarow9.GetCell(2) != null)
                        {
                            if (datarow9.GetCell(2).CellType == CellType.Formula)
                            {
                                pageModel.PeopleNum_woman = (int)(datarow9.GetCell(2).NumericCellValue);
                            }
                            else
                            {
                                if (DataCheck.isNumber(datarow9.GetCell(2).ToString()))
                                {
                                    pageModel.PeopleNum_woman = int.Parse(datarow9.GetCell(2).ToString());
                                }

                            }
                        }
                        else
                        {
                            pageModel.PeopleNum_woman = 0;
                        }




                        HSSFRow datarow10 = (HSSFRow)sheet.GetRow(14);
                        if (datarow10.GetCell(2) != null)
                        {
                            if (datarow10.GetCell(2).CellType == CellType.Formula)
                            {
                                pageModel.PeopleNum_age1 = (int)(datarow10.GetCell(2).NumericCellValue);
                            }
                            else
                            {
                                if (DataCheck.isNumber(datarow10.GetCell(2).ToString()))
                                {
                                    pageModel.PeopleNum_age1 = int.Parse(datarow10.GetCell(2).ToString());
                                }

                            }

                        }
                        else
                        {
                            pageModel.PeopleNum_age1 = 0;
                        }



                        HSSFRow datarow11 = (HSSFRow)sheet.GetRow(15);
                        if (datarow11.GetCell(2) != null)
                        {
                            if (datarow11.GetCell(2).CellType == CellType.Formula)
                            {
                                pageModel.PeopleNum_age2 = (int)(datarow11.GetCell(2).NumericCellValue);
                            }
                            else
                            {
                                if (DataCheck.isNumber(datarow11.GetCell(2).ToString()))
                                {
                                    pageModel.PeopleNum_age2 = int.Parse(datarow11.GetCell(2).ToString());
                                }

                            }
                        }
                        else
                        {
                            pageModel.PeopleNum_age2 = 0;
                        }




                    HSSFRow datarow12 = (HSSFRow)sheet.GetRow(16);
                        if (datarow12.GetCell(2) != null)
                        {
                            if (datarow12.GetCell(2).CellType == CellType.Formula)
                            {
                                pageModel.PeopleNum_age3 = (int)(datarow12.GetCell(2).NumericCellValue);
                            }
                            else
                            {
                                if (DataCheck.isNumber(datarow12.GetCell(2).ToString()))
                                {
                                    pageModel.PeopleNum_age3 = int.Parse(datarow12.GetCell(2).ToString());
                                }

                            }
                        }
                        else
                        {
                            pageModel.PeopleNum_age3 = 0;
                        }




                    HSSFRow datarow13 = (HSSFRow)sheet.GetRow(17);
                        if (datarow13.GetCell(2) != null)
                        {
                            if (datarow13.GetCell(2).CellType == CellType.Formula)
                            {
                                pageModel.PeopleNum_age4 = (int)(datarow13.GetCell(2).NumericCellValue);
                            }
                            else
                            {
                                if (DataCheck.isNumber(datarow13.GetCell(2).ToString()))
                                {
                                    pageModel.PeopleNum_age4 = int.Parse(datarow13.GetCell(2).ToString());
                                }

                            }
                        }
                        else
                        {
                            pageModel.PeopleNum_age4 = 0;
                        }




                    HSSFRow datarow14 = (HSSFRow)sheet.GetRow(19);
                        if (datarow14.GetCell(2) != null)
                        {
                            if (datarow14.GetCell(2).CellType == CellType.Formula)
                            {
                                pageModel.PeopleNum_xlss = (int)(datarow14.GetCell(2).NumericCellValue);
                            }
                            else
                            {
                                if (DataCheck.isNumber(datarow14.GetCell(2).ToString()))
                                {
                                    pageModel.PeopleNum_xlss = int.Parse(datarow14.GetCell(2).ToString());
                                }

                            }
                        }
                        else
                        {
                            pageModel.PeopleNum_xlss = 0;
                        }




                        HSSFRow datarow15 = (HSSFRow)sheet.GetRow(20);
                        if (datarow15.GetCell(2) != null)
                        {
                            if (datarow15.GetCell(2).CellType == CellType.Formula)
                            {
                                pageModel.PeopleNum_xlbk = (int)(datarow15.GetCell(2).NumericCellValue);
                            }
                            else
                            {
                                if (DataCheck.isNumber(datarow15.GetCell(2).ToString()))
                                {
                                    pageModel.PeopleNum_xlbk = int.Parse(datarow15.GetCell(2).ToString());
                                }

                            }
                        }
                        else
                        {
                            pageModel.PeopleNum_xlbk = 0;
                        }




                        HSSFRow datarow16 = (HSSFRow)sheet.GetRow(21);
                        if (datarow16.GetCell(2) != null)
                        {
                            if (datarow16.GetCell(2).CellType == CellType.Formula)
                            {
                                pageModel.PeopleNum_xldz = (int)(datarow16.GetCell(2).NumericCellValue);
                            }
                            else
                            {
                                if (DataCheck.isNumber(datarow16.GetCell(2).ToString()))
                                {
                                    pageModel.PeopleNum_xldz = int.Parse(datarow16.GetCell(2).ToString());
                                }

                            }
                        }
                        else
                        {
                            pageModel.PeopleNum_xldz = 0;
                        }




                        HSSFRow datarow17 = (HSSFRow)sheet.GetRow(22);
                        if (datarow17.GetCell(2) != null)
                        {
                            if (datarow17.GetCell(2).CellType == CellType.Formula)
                            {
                                pageModel.PeopleNum_xlzz = (int)(datarow17.GetCell(2).NumericCellValue);
                            }
                            else
                            {
                                if (DataCheck.isNumber(datarow17.GetCell(2).ToString()))
                                {
                                    pageModel.PeopleNum_xlzz = int.Parse(datarow17.GetCell(2).ToString());
                                }

                            }
                        }
                        else
                        {
                            pageModel.PeopleNum_xlzz = 0;
                        }




                        HSSFRow datarow18 = (HSSFRow)sheet.GetRow(23);
                        if (datarow18.GetCell(2) != null)
                        {
                            if (datarow18.GetCell(2).CellType == CellType.Formula)
                            {
                                pageModel.PeopleNum_xlqt = (int)(datarow18.GetCell(2).NumericCellValue);
                            }
                            else
                            {
                                if (DataCheck.isNumber(datarow18.GetCell(2).ToString()))
                                {
                                    pageModel.PeopleNum_xlqt = int.Parse(datarow18.GetCell(2).ToString());
                                }

                            }
                        }
                        else
                        {
                            pageModel.PeopleNum_xlqt = 0;
                        }




                        HSSFRow datarow19 = (HSSFRow)sheet.GetRow(25);
                        if (datarow19.GetCell(2) != null)
                        {
                            if (datarow19.GetCell(2).CellType == CellType.Formula)
                            {
                                pageModel.PeopleNum_zczgj = (int)(datarow19.GetCell(2).NumericCellValue);
                            }
                            else
                            {
                                if (DataCheck.isNumber(datarow19.GetCell(2).ToString()))
                                {
                                    pageModel.PeopleNum_zczgj = int.Parse(datarow19.GetCell(2).ToString());
                                }

                            }
                        }
                        else
                        {
                            pageModel.PeopleNum_zczgj = 0;
                        }




                        HSSFRow datarow20 = (HSSFRow)sheet.GetRow(26);
                        if (datarow20.GetCell(2) != null)
                        {
                            if (datarow20.GetCell(2).CellType == CellType.Formula)
                            {
                                pageModel.PeopleNum_zcfgj = (int)(datarow20.GetCell(2).NumericCellValue);
                            }
                            else
                            {
                                if (DataCheck.isNumber(datarow20.GetCell(2).ToString()))
                                {
                                    pageModel.PeopleNum_zcfgj = int.Parse(datarow20.GetCell(2).ToString());
                                }

                            }
                        }
                        else
                        {
                            pageModel.PeopleNum_zcfgj = 0;
                        }




                        HSSFRow datarow21 = (HSSFRow)sheet.GetRow(27);
                        if (datarow21.GetCell(2) != null)
                        {
                            if (datarow21.GetCell(2).CellType == CellType.Formula)
                            {
                                pageModel.PeopleNum_zczj = (int)(datarow21.GetCell(2).NumericCellValue);
                            }
                            else
                            {
                                if (DataCheck.isNumber(datarow21.GetCell(2).ToString()))
                                {
                                    pageModel.PeopleNum_zczj = int.Parse(datarow21.GetCell(2).ToString());
                                }

                            }
                        }
                        else
                        {
                            pageModel.PeopleNum_zczj = 0;
                        }




                        HSSFRow datarow22 = (HSSFRow)sheet.GetRow(28);
                        if (datarow22.GetCell(2) != null)
                        {
                            if (datarow22.GetCell(2).CellType == CellType.Formula)
                            {
                                pageModel.PeopleNum_zccj = (int)(datarow22.GetCell(2).NumericCellValue);
                            }
                            else
                            {
                                if (DataCheck.isNumber(datarow22.GetCell(2).ToString()))
                                {
                                    pageModel.PeopleNum_zccj = int.Parse(datarow22.GetCell(2).ToString());
                                }

                            }
                        }
                        else
                        {
                            pageModel.PeopleNum_zccj = 0;
                        }




                        HSSFRow datarow23 = (HSSFRow)sheet.GetRow(29);
                        if (datarow23.GetCell(2) != null)
                        {
                            if (datarow23.GetCell(2).CellType == CellType.Formula)
                            {
                                pageModel.PeopleNum_zcqt = (int)(datarow23.GetCell(2).NumericCellValue);
                            }
                            else
                            {
                                if (DataCheck.isNumber(datarow23.GetCell(2).ToString()))
                                {
                                    pageModel.PeopleNum_zcqt = int.Parse(datarow23.GetCell(2).ToString());
                                }

                            }
                        }
                        else
                        {
                            pageModel.PeopleNum_zcqt = 0;
                        }
                        //公式
                        int tempInstituNum = 0;
                        if (pageModel.InstituNum_Area != null)
                        {
                            tempInstituNum += (int)pageModel.InstituNum_Area;
                        }
                        if (pageModel.InstituNum_City != null)
                        {
                            tempInstituNum += (int)pageModel.InstituNum_City;
                        }
                        if (pageModel.InstituNum_town != null)
                        {
                            tempInstituNum += (int)pageModel.InstituNum_town;
                        }
                        if (tempInstituNum != 0)
                        {
                            pageModel.InstituNum = tempInstituNum;
                        }
                        //按年龄分
                        int tempPeopleNum = 0;
                        if (pageModel.PeopleNum_age1 != null)
                        {
                            tempPeopleNum += (int)pageModel.PeopleNum_age1;
                        }
                        if (pageModel.PeopleNum_age2 != null)
                        {
                            tempPeopleNum += (int)pageModel.PeopleNum_age2;
                        }
                        if (pageModel.PeopleNum_age3 != null)
                        {
                            tempPeopleNum += (int)pageModel.PeopleNum_age3;
                        }
                        if (pageModel.PeopleNum_age4 != null)
                        {
                            tempPeopleNum += (int)pageModel.PeopleNum_age4;
                        }


                        //按州市、县区分
                        int PeopleNum1 = 0;
                        if (pageModel.PeopleNum_City != null)
                        {
                            PeopleNum1 += (int)pageModel.PeopleNum_City;
                        }
                        if (pageModel.PeopleNum_County != null)
                        {
                            PeopleNum1 += (int)pageModel.PeopleNum_County;
                        }

                        //按性别分
                        int PeopleNum2 = 0;
                        if (pageModel.PeopleNum_man != null)
                        {
                            PeopleNum2 += (int)pageModel.PeopleNum_man;
                        }
                        if (pageModel.PeopleNum_woman != null)
                        {
                            PeopleNum2 += (int)pageModel.PeopleNum_woman;
                        }

                        //按学历层次分
                        int PeopleNum3 = 0;
                        if (pageModel.PeopleNum_xlbk != null)//本科
                        {
                            PeopleNum3 += (int)pageModel.PeopleNum_xlbk;
                        }
                        if (pageModel.PeopleNum_xldz != null)//大专
                        {
                            PeopleNum3 += (int)pageModel.PeopleNum_xldz;
                        }
                        if (pageModel.PeopleNum_xlqt != null)//其他
                        {
                            PeopleNum3 += (int)pageModel.PeopleNum_xlqt;
                        }
                        if (pageModel.PeopleNum_xlss != null)//硕士
                        {
                            PeopleNum3 += (int)pageModel.PeopleNum_xlss;
                        }
                        if (pageModel.PeopleNum_xlzz != null)//中专
                        {
                            PeopleNum3 += (int)pageModel.PeopleNum_xlzz;
                        }

                        //按职称分
                        int PeopleNum4 = 0;
                        if (pageModel.PeopleNum_zccj != null)//初级职称
                        {
                            PeopleNum4 += (int)pageModel.PeopleNum_zccj;
                        }
                        if (pageModel.PeopleNum_zcfgj != null)//副高级
                        {
                            PeopleNum4 += (int)pageModel.PeopleNum_zcfgj;
                        }
                        if (pageModel.PeopleNum_zcqt != null)//其他
                        {
                            PeopleNum4 += (int)pageModel.PeopleNum_zcqt;
                        }
                        if (pageModel.PeopleNum_zczgj != null)//正高级
                        {
                            PeopleNum4 += (int)pageModel.PeopleNum_zczgj;
                        }
                        if (pageModel.PeopleNum_zczj != null)//中级
                        {
                            PeopleNum4 += (int)pageModel.PeopleNum_zczj;
                        }
                        if (pageModel.PeopleNum != tempPeopleNum)
                        {
                            obj.status = DefaultKey.ErrorResult;
                            obj.message = "总人数不等于年龄子项之和！";
                            obj.model = 1;
                            return obj;
                        }
                        if (pageModel.PeopleNum != PeopleNum1)
                        {
                            obj.status = DefaultKey.ErrorResult;
                            obj.message = "总人数不等于区域子项之和！";
                            obj.model = 2;
                            return obj;
                        }
                        if (pageModel.PeopleNum != PeopleNum2)
                        {
                            obj.status = DefaultKey.ErrorResult;
                            obj.message = "总人数不等于性别子项之和！";
                            obj.model = 3;
                            return obj;
                        }
                        if (pageModel.PeopleNum != PeopleNum3)
                        {
                            obj.status = DefaultKey.ErrorResult;
                            obj.message = "总人数不等于学历层次子项之和！";
                            obj.model = 4;
                            return obj;
                        }
                        if (pageModel.PeopleNum != PeopleNum4)
                        {
                            obj.status = DefaultKey.ErrorResult;
                            obj.message = "总人数不等于职称子项之和！";
                            obj.model = 5;
                            return obj;
                        }

                        //判断list中是否初始化过报表
                        if (reportServices.CheckRepCount(ReportId,"T_PotPromotion"))
                        {
                            //没初始化过进来
                            potPromotionServices.AddInfo(pageModel);
                        }
                        else
                        {
                            potPromotionServices.UpdateInfo(pageModel);
                        }
                        obj.status = 1;
                        obj.message = "";
                        obj.model = 5;
                    Directory.Delete(temppath, true);
                }
            }
            return obj;
        }
        /// <summary>
        /// 获取统计列表
        /// </summary>
        /// <param name="AreaCode"></param>
        /// <param name="FillInYear"></param>
        /// <returns></returns>
        public ResResult<List<PromotionStatisModel>> GetStatisListData(string AreaCode, string FillInYear)
        {
            return potPromotionServices.getStatisListData(AreaCode, FillInYear);
        }

        /// <summary>
        /// 统计导出
        /// </summary>
        /// <param name="AreaCode"></param>
        /// <param name="FillInYear"></param>
        public void GetStatisExcel(string AreaCode, string FillInYear)
        {
            AreaServices areaServices = new AreaServices();
            string AreaName = areaServices.GetByCodeModel(AreaCode).model.Name;
            //打开完成excel文件并下载
            HttpContext curContext = HttpContext.Current;
            // 设置编码和附件格式
            curContext.Response.AddHeader("Access-Control-Allow-Origin", "*");
            curContext.Response.ContentType = "application/vnd.ms-excel";
            curContext.Response.ContentEncoding = Encoding.UTF8;
            curContext.Response.Charset = "";
            string strFileName = FillInYear + "年-" + AreaName + "-马铃薯服务机构人员情况汇总表.xls";
            curContext.Response.AppendHeader("Content-Disposition",
                "attachment;filename=" + HttpUtility.UrlEncode(strFileName, Encoding.UTF8));
            //curContext.Response.AppendHeader("Content-Disposition",
            //   "attachment;filename=" + strFileName);
            curContext.Response.BinaryWrite(loadStatis(AreaCode, FillInYear).GetBuffer());
            curContext.Response.End();
        }

        public MemoryStream loadStatis(string AreaCode, string FillInYear)
        {
            FileStream file = null;
            //创建下载Excel文件
            var workbook = new HSSFWorkbook();
            string fileIndex = HttpRuntime.AppDomainAppPath.ToString();
            string filename = @"tempfile\Basic" + DateTime.Now.Ticks.ToString() + ".xls";//临时文件，定期在tempfile手动删除
            string tempExcel = fileIndex + filename;//临时文件路径及全称
            string templateName = fileIndex + @"Excel\PotStatisLoad.xls";//模板文件
            file = new FileStream(templateName, FileMode.Open, FileAccess.Read);//读模板文件
            HSSFWorkbook templateBook = new HSSFWorkbook(file);//读取模板文件信息转换成Excel工作薄
            HSSFSheet tempsheet = templateBook.GetSheet("PotPromotion") as HSSFSheet;//读取模板所在sheet位置
            List<PromotionStatisModel> pagelist = (List<PromotionStatisModel>)potPromotionServices.getStatisListData(AreaCode, FillInYear).model;//数据获取

            HSSFRow row = (HSSFRow)tempsheet.GetRow(4);//读取模板第5行
            var style = row.GetCell(1).CellStyle;//读取模板第5行第2单元格样式并保存
            row.GetCell(1).CellStyle = style;//对第3行第2单元格设置原来样式

            HSSFSheet sheet = CopyToSheet(tempsheet, pagelist);
            AreaServices areaServices = new AreaServices();
            string areaName = areaServices.GetByCodeModel(AreaCode).model.Name;
            sheet.CopyTo(workbook, areaName, true, true);

            //if (AreaCode.Equals("530000000000"))
            //{
            //    var citys = areaServices.GetPrefectureList();
            //    foreach (string areacode in citys)
            //    {
            //        List<PromotionStatisModel> citylist = potPromotionServices.getStatisList(areacode, FillInYear);
            //        FileStream fm = new FileStream(templateName, FileMode.Open, FileAccess.Read);
            //        HSSFWorkbook Book = new HSSFWorkbook(fm);
            //        HSSFSheet temp = Book.GetSheet("PotPromotion") as HSSFSheet;
            //        HSSFRow row1 = (HSSFRow)temp.GetRow(4);
            //        HSSFSheet sheet1 = CopyToSheet(temp, citylist);
            //        string areaName1 = areaServices.GetByCodeModel(areacode).model.Name;
            //        sheet1.CopyTo(workbook, areaName1, true, true);
            //    }
            //}

            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return ms;
            }
        }

        public HSSFSheet CopyToSheet(HSSFSheet sheet, List<PromotionStatisModel> list)
        {
            HSSFRow row = (HSSFRow)sheet.GetRow(4);
            if (list.Count > 0)
            {
                for (int i = 0; i < list.Count; i++)
                {
                    HSSFRow datarow = (HSSFRow)sheet.CreateRow(4 + i);
                    datarow.CreateCell(0).SetCellValue(list[i].AreaName == null ? "" : list[i].AreaName);
                    datarow.CreateCell(1).SetCellValue(list[i].InstituNum == null ? "" : list[i].InstituNum.ToString());
                    datarow.CreateCell(2).SetCellValue(list[i].InstituNum_City == null ? "" : list[i].InstituNum_City.ToString());
                    datarow.CreateCell(3).SetCellValue(list[i].InstituNum_Area == null ? "" : list[i].InstituNum_Area.ToString());
                    datarow.CreateCell(4).SetCellValue(list[i].InstituNum_town == null ? "" : list[i].InstituNum_town.ToString());
                    datarow.CreateCell(5).SetCellValue(list[i].PeopleNum == null ? "" : list[i].PeopleNum.ToString());
                    datarow.CreateCell(6).SetCellValue(list[i].PeopleNum_County == null ? "" : list[i].PeopleNum_County.ToString());
                    datarow.CreateCell(7).SetCellValue(list[i].PeopleNum_City == null ? "" : list[i].PeopleNum_City.ToString());
                    datarow.CreateCell(8).SetCellValue(list[i].PeopleNum_man == null ? "" : list[i].PeopleNum_man.ToString());
                    datarow.CreateCell(9).SetCellValue(list[i].PeopleNum_woman == null ? "" : list[i].PeopleNum_woman.ToString());
                    datarow.CreateCell(10).SetCellValue(list[i].PeopleNum_age1 == null ? "" : list[i].PeopleNum_age1.ToString());
                    datarow.CreateCell(11).SetCellValue(list[i].PeopleNum_age2 == null ? "" : list[i].PeopleNum_age2.ToString());
                    datarow.CreateCell(12).SetCellValue(list[i].PeopleNum_age3 == null ? "" : list[i].PeopleNum_age3.ToString());
                    datarow.CreateCell(13).SetCellValue(list[i].PeopleNum_age4 == null ? "" : list[i].PeopleNum_age4.ToString());
                    datarow.CreateCell(14).SetCellValue(list[i].PeopleNum_xlss == null ? "" : list[i].PeopleNum_xlss.ToString());
                    datarow.CreateCell(15).SetCellValue(list[i].PeopleNum_xlbk == null ? "" : list[i].PeopleNum_xlbk.ToString());
                    datarow.CreateCell(16).SetCellValue(list[i].PeopleNum_xldz == null ? "" : list[i].PeopleNum_xldz.ToString());
                    datarow.CreateCell(17).SetCellValue(list[i].PeopleNum_xlzz == null ? "" : list[i].PeopleNum_xlzz.ToString());
                    datarow.CreateCell(18).SetCellValue(list[i].PeopleNum_xlqt == null ? "" : list[i].PeopleNum_xlqt.ToString());
                    datarow.CreateCell(19).SetCellValue(list[i].PeopleNum_zczgj == null ? "" : list[i].PeopleNum_zczgj.ToString());
                    datarow.CreateCell(20).SetCellValue(list[i].PeopleNum_zcfgj == null ? "" : list[i].PeopleNum_zcfgj.ToString());
                    datarow.CreateCell(21).SetCellValue(list[i].PeopleNum_zczj == null ? "" : list[i].PeopleNum_zczj.ToString());
                    datarow.CreateCell(22).SetCellValue(list[i].PeopleNum_zccj == null ? "" : list[i].PeopleNum_zccj.ToString());
                    datarow.CreateCell(23).SetCellValue(list[i].PeopleNum_zcqt == null ? "" : list[i].PeopleNum_zcqt.ToString());

                    for (int j = 0; j <= 23; j++)
                    {
                        datarow.GetCell(j).CellStyle = row.GetCell(j).CellStyle;
                    }

                }
            }
            return sheet;
        }

    }
}